package net.xju3_4.dao;


import net.xju3_4.common.utils.JdbcUtilsV1;
import net.xju3_4.common.utils.PageInfo;
import net.xju3_4.entity.PaymentEntity;

import java.sql.Connection;
import java.util.List;

public class merchantPaymentDao {
    public static List<PaymentEntity> selectAll(String month, String year, String merchantId) {
        //    订单分页查找
        Connection conn = JdbcUtilsV1.getConnection();
        String sql = "SELECT  " +
                "    p.id AS paymentId, " +
                "    p.order_id orderId, " +
                "    p.payment_no paymentNo, " +
                "    p.payment_type paymentType, " +
                "    p.amount , " +
                "    p.payment_time paymentTime, " +
                "    p.refund_time refundTime,  " +
                "    p.status status " +
                "FROM  " +
                "    payment p " +
                "JOIN rental_order ro ON p.order_id = ro.id " +
                "WHERE 1=1 and ro.merchant_id="+merchantId;
        if (month != null && !month.isEmpty()){
            sql+=" and p.payment_time>='"+year+"-"+month+"-01'" + " AND p.payment_time<='"+year+"-"+month+"-28'";
        }
        if (year != null && !year.isEmpty()){
            sql+=" and p.payment_time>='"+year+"-01-01'" + " AND p.payment_time<='"+year+"-12-31'";
        }
        return JdbcUtilsV1.executeQuery(conn, PaymentEntity.class,sql);
    }

// 查询指定商户指定年份的支付信息

    public static List<PaymentEntity> selectYear(String merchantId) {
        Connection conn = JdbcUtilsV1.getConnection();
//        按年划分查询sql
        String sql2 = "SELECT " +
                "  YEAR(payment_time) AS `year`,"+
                "  COUNT(*) AS totalOrder,"+
                "  SUM(amount) AS totalAmount," +
                "  count(refund_time) totalRefundTime"+
                " FROM"+
                " payment p"+
                " JOIN rental_order ro ON p.order_id = ro.id"+
                " WHERE "+
                " ro.merchant_id =" +merchantId+
                " GROUP BY " +
                " YEAR(payment_time)"+
                " ORDER BY "+
                " `year` DESC;";
            return JdbcUtilsV1.executeQuery(conn, PaymentEntity.class,sql2);

    }
// 查询指定商户指定月份的支付信息
    public static List<PaymentEntity> selectMonth(String merchantId) {
        Connection conn = JdbcUtilsV1.getConnection();
//        月划分查询sql
        String sql1 = " SELECT " +
                "  YEAR(payment_time) AS `year`," +
                "  MONTH(payment_time) AS `month`," +
                "  COUNT(*) AS totalOrder," +
                "  SUM(amount) AS totalAmount," +
                "  count(refund_time) totalRefundTime" +
                "  FROM " +
                "  payment p" +
                "  JOIN rental_order ro ON p.order_id = ro.id" +
                "  WHERE " +
                "  ro.merchant_id =" +merchantId+
                "  GROUP BY " +
                "  YEAR(payment_time), " +
                "  MONTH(payment_time)     " +
                "  ORDER BY " +
                "  `year` DESC, `month` DESC;  ";
            return JdbcUtilsV1.executeQuery(conn, PaymentEntity.class,sql1);
    }
    public PageInfo<PaymentEntity> PageList(String paymentType, String paymentTime, String status, String merchantId, String returnTime, String amount, int pageNum, int pageSize) {

        //    订单分页查找
            Connection conn = JdbcUtilsV1.getConnection();
            String sql = "SELECT  " +
                    "    p.id AS paymentId, " +
                    "    p.order_id orderId, " +
                    "    p.payment_no paymentNo, " +
                    "    p.payment_type paymentType, " +
                    "    p.amount , " +
                    "    p.payment_time paymentTime, " +
                    "    p.refund_time refundTime,  " +
                    "    p.status status " +
                    "FROM  " +
                    "    payment p " +
                    "JOIN rental_order ro ON p.order_id = ro.id " +
                    "WHERE 1=1 ";
            if (paymentType != null && !paymentType.isEmpty()) {
                sql += (" AND p.payment_type =") + (paymentType) ;
            }

            if (paymentTime != null && !paymentTime.isEmpty()) {
                sql += (" AND p.payment_time=  '") + (paymentTime) + ("'");
            }
            if (merchantId != null && !merchantId.isEmpty()) {
                sql += (" AND ro.merchant_id = ") + (merchantId);
            }
            if (status != null && !status.isEmpty()) {
                sql += (" AND p.status = '") + (status) + ("'");
            }
            if (returnTime != null && !returnTime.isEmpty()) {
                sql += (" AND p.refund_time = '") + (returnTime) + ("'");
            }
            if (amount != null && !amount.isEmpty()) {
                sql += (" AND p.amount = ") + (amount);
            }
            sql += " order by p.payment_time desc";
            sql += " limit " + (pageNum - 1) * pageSize + "," + pageSize;
            List<PaymentEntity> users = JdbcUtilsV1.executeQuery(conn, PaymentEntity.class, sql);
            String countSql = "select count(*) from payment p join rental_order ro ON p.order_id = ro.id where ro.merchant_id="+merchantId;
            long count = JdbcUtilsV1.executeQueryOneValue(countSql);
            PageInfo<PaymentEntity> pageInfo = new PageInfo<>(pageNum, pageSize, (int) count, users);
            return pageInfo;
        }
}
